First of all answer this question : Which method of T-SQL is better for performance LEFT JOIN or NOT IN when writing a query? The answer is: It depends! It all depends on what kind of data is and what kind query it is etc. In that case just for fun guess one option LEFT JOIN or NOT IN. If you need to refer the query which demonstrates the mentioned clauses, review following two queries for Join Better Performance.
USE AdventureWorks; GO SELECT ProductID FROM Production.Product WHERE ProductID NOT IN ( SELECT ProductID FROM Production.WorkOrder); GO SELECT p.ProductID FROM Production.Product p LEFT JOIN Production.WorkOrder w ON p.ProductID = w.ProductID WHERE w.ProductID IS NULL; GO
Now let us examine the actual execution plan of both the queries. Click on image to see larger image.
You can clearly observe that first query with a NOT IN takes 20% resources of the execution plan and LEFT JOIN takes 80% resources of execution plan. It is better to use NOT IN clause over LEFT JOIN in this particular example. Please note that this is not generic conclusion and applies to this example only. Your results may vary on many factors. Let me know your comments if you have guessed this correct or not.
Reference : Pinal Dave (https://blog.sqlauthority.com)
70 Comments. Leave new
can anyone explain me the case when should i use which join.Because most of the time i did normal joins using where clauses so i am not use to any other kind of join
@Dhiraj – your question is confusing. Maybe this will help you:
Use an INNER JOIN when you want only records that are related in both tables.
Use a LEFT JOIN when you want all records in the left table.
Use a RIGHT JOIN when you want all records in the right table.
Personally, I never use RIGHT JOIN. I’ve written thousands of queries with just INNER or LEFT.
Hope this helps.
HI pinal, SQL guru, i am your lovable fan in SQL sever 2005
Can you answer or suggest my question?
is Any Alter Way of Leftouter join method Available in Sequel?
Can anyone give me the method of alter way of left outer join Because when we create a indexed view we could not able create index on theat view due to statement contains FULL JOIN or LEFT JOIN or RIGHT JOIN. So i searched the web fully it gives suggestion using UNION Instead of left join,I mentioned query below. So could you give me any other method is available to replace LEFT JOIN with that other than UNION? Is it full to reduce optimization cost when we use UNION instead of LEFT JOIN? Quick suggestions are highly appreciated………….buddys………
———————-My original table using left join method—————–
SELECT tablea.*,
CASE
WHEN tableb.tablebcolumn1 IS NULL THEN 0
ELSE 1
END IsHosted
FROM tablea
LEFT JOIN tableb ON tablea.column=tableb.column
———–Example of alter way of using UNIONALL instead of leftouterjoin ———
SELECT tablea.,
CASE
WHEN tableb.tablebcolumn1 IS NULL THEN 0
ELSE 1
END IsHosted
FROM tablea where tablea.column IN (select tableb.column from tableb) UNION ALL SELECT tablea.,
CASE
WHEN tableb.tablebcolumn1 IS NULL THEN 0
ELSE 1
END IsHosted
FROM tablea where tablea.column NOT IN (select tableb.column from tableb)
————————————————————————————– Note: Due to security reason i did not give original table name. i gave table name of tablea and tableb………as my originaltable name
I am just curious why sometimes ‘IN’ and ‘NOT IN’ give different result than ‘JOIN’
Sometimes, using IN/NOT IN with the same table work but doesn’t with another table (same table structure)
select email from [temp].[dbo].SETA where email not in (select email from [temp].[dbo].SETA) — OK
select email from [temp].[dbo].SETA where email not in (select email from [temp].[dbo].SETB) — return nothing both ‘not in’ and ‘in’
Hi, I am Meenakshi, I have one query
How to write the query for having more than 1000 static values in IN clause?
Or any alternative solution to IN clause?
In this scenario, I would import the list into a sql table using the Import Wizard, then use an inner join on the table that contains the data. If you need help writing the query you can contact me.
Hello Panil, one of my query is taking long time to execute.. Can you think of other way to rewrite this query? It used to take like 4 min now it is taking 20 min.. some service pack and indexes were installed on the server and since then we are having this issue…
SELECT * From #Temp Where ID not in
(Select CODE from Event where #Temp.ID = Event.ID AND #Temp.Class = Event.Class)
Hello Panil, I have a query, i tried a lot but couldn’t findout the results. Could you please help me on this query. Need UnMatched Locations from Table1 and Table2, But ID should be matched
Create table #t1(T1ID int,T1Type varchar(10) null,T1Locations varchar(10) null)
Create table #t2(T2ID int,T2Type varchar(10) null,T2Locations varchar(10) null)
Insert into #t1 select 101,’Primary’,’HYD’
Insert into #t1 select 101,’Secondary’,’Calcutta’
Insert into #t1 select 101,’Secondary’,’Mumbai’
Insert into #t1 select 102,’Secondary’,’Calcutta’
Insert into #t1 select 102,’Secondary’,’Banglore’
Insert into #t1 select 103,’Secondary’,’Delhi’
Insert into #t1 select 103,’Secondary’,’Mumbai’
Insert into #t1 select 105,’Secondary’,’Delhi’
Insert into #t2 select 101,’Primary’,’HYD’
Insert into #t2 select 101,’Secondary’,’Calcutta’
Insert into #t2 select 101,’Secondary’,’Banglore’
Insert into #t2 select 102,’Secondary’,’Banglore’
Insert into #t2 select 102,’Secondary’,’Delhi’
Insert into #t2 select 103,’Primary’,’HYD’
Insert into #t2 select 103,’Secondary’,’Delhi’
Insert into #t2 select 104,’Secondary’,’Delhi’
Result should be (Matched with ID but Locations are unmatched)
101,’Secondary’,’Mumbai’
101,’Secondary’,’Banglore’
102,’Secondary’,’Calcutta’
102,’Secondary’,’Delhi’
103,’Secondary’,’Mumbai’
103,’Primary’,’HYD’
Appreciate your Help.
Thank you,
Kranti
select t1.* from #t1 t1
where cast(t1.T1ID as varchar(10))+T1Locations not in(select cast(T2ID as varchar(10))+T2Locations from #t2 )
union
select t1.* from #t2 t1
where cast(t1.T2ID as varchar(10))+T2Locations not in(select cast(T1ID as varchar(10))+T1Locations from #t1 )
If you have a lot of data in the table Production.WorkOrder, the left join query will be the fastest. Only because the seek in Production.WorkOrder take 93% compare to 42% in the the join case.
Works great. I was joining between a daily transaction table with 30 million rows and a master table to check if there are any entries.IN operator took quite a long time to execute , but left join ran in just 2 mins.
Thanks
i have million of data in sql server 2005 how to execute less time ple reply its very urgent
You need to look at query execution plan and find for costly operators. Then optimize them.
which type of the join can be given better performance among those joins like inner join,left Join or right join?????
gauys anyone know plz give me the answers..
Hi Pinal , As always there is no best way write query so there is always better to write in more and more readable and easily understanding by other,
for this particular query. both are actually produce different logical tree and coz of this particular schema they end up having different final execution plan ,
look at the query plan With not IN there is TOP iterator on the outer side of NL join that is interesting and have a solid purpose to be there its actually passing 1 row per outer row , because Index IX_WorkOrder_ProductID can not guarantee unique row , so it check by top iterator.
NL join are quite interesting and are complicated that other physical join iterator.
If the result set in the sub query table is small result (small lokup table for example) it doesn’t matter to use IN operator but I prefer using exists operator
All depends on query optimizer.
yes not in more faster then left join, but not in can’t put in more complex query with many condition
You can compare performance between INNER and LEFT join?
Not really that will bring different results.